package org.duang.kit;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;
import org.duang.config.Config;
import org.duang.db.IdEntity;

public class ExcelKit {

	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	private static DecimalFormat df = new DecimalFormat("0");

	// private static ExcelKit<?> excelKit;
	public static void main(String[] args) throws Exception {

		File file = new File("ExcelDemo.xls");

		String[][] result = getData(file, 1);

		int rowLength = result.length;

		for (int i = 0; i < rowLength; i++) {

			for (int j = 0; j < result[i].length; j++) {

				System.out.print(result[i][j] + "\t\t");

			}

			System.out.println();

		}

	}

	// @SuppressWarnings("rawtypes")
	// public ExcelKit duang(T t) {
	// excelKit = new ExcelKit<T>();
	// return excelKit;
	// }

	/**
	 * 
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * 
	 * @param file
	 *            读取数据的源Excel
	 * 
	 * @param ignoreRows
	 *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * 
	 * @return 读出的Excel中数据的内容
	 * 
	 * @throws FileNotFoundException
	 * 
	 * @throws IOException
	 * 
	 */

	@SuppressWarnings("resource")
	public static String[][] getData(File file, int ignoreRows) throws FileNotFoundException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		Workbook wb = null;
		BufferedInputStream in = null;
		try {
			wb = new XSSFWorkbook(file);
		} catch (Exception ex) {
			in = new BufferedInputStream(new FileInputStream(file));
			// 打开HSSFWorkbook
			POIFSFileSystem fs = new POIFSFileSystem(in);
			wb = new HSSFWorkbook(fs);
		}

		Cell cell = null;
		int numberOfSheets = wb.getNumberOfSheets();
		for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
			Sheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				Row row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				int lastCellNum = row.getLastCellNum();
				for (short columnIndex = 0; columnIndex <= lastCellNum; columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = sdf.format(date);
								} else {
									value = "";
								}
							} else {
								value = df.format(cell.getNumericCellValue());
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;

						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y" : "N");
							break;
						default:
							value = "";
						}
					}
					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}
		if (in != null)
			in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}

	/**
	 * 
	 * 去掉字符串右边的空格
	 * 
	 * @param str
	 *            要处理的字符串
	 * 
	 * @return 处理后的字符串
	 * 
	 */

	private static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);

	}

	/**
	 * 导出Excel
	 * 
	 * @param columnMap
	 *            LinkedHashMap集合<字段名，显示名><br/>
	 *            例： <name, "姓名">
	 * @param results
	 *            查询结果数据源
	 * @param filePath
	 *            文件路径
	 */
	public static String buildXls(LinkedHashMap<String, String> columnMap, List<?> results) {
		return buildXls("Sheet1", columnMap, results);
	}

	/**
	 * 导出Excel
	 * 
	 * @param sheetName
	 * 			 excel工作簿名称
	 * @param columnMap
	 *            LinkedHashMap集合<字段名，显示名><br/>
	 *            例： <name, "姓名">
	 * @param results
	 *            查询结果数据源
	 * @param filePath
	 *            文件路径
	 */
	@SuppressWarnings("resource")
	public static String buildXls(String sheetName, LinkedHashMap<String, String> columnMap, List<?> results) {
		HSSFWorkbook workbook = new HSSFWorkbook(); // 创建了一个excel文件
		HSSFSheet sheet = workbook.createSheet(sheetName); // 创建了一个工作簿
		HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
		int columnSize = columnMap.size();
		HSSFCell[] firstcell = new HSSFCell[columnSize];
		int index = 0;
		Set<String> keySet = columnMap.keySet();
		for (Iterator<String> it = keySet.iterator(); it.hasNext();) {
			String key = it.next();
			firstcell[index] = firstrow.createCell(index);
			firstcell[index].setCellType(HSSFCell.CELL_TYPE_STRING);
			firstcell[index].setCellValue(new HSSFRichTextString(columnMap.get(key)));
			index++;
		}

		int rownum = 1;
		HSSFRow row = null;
		HSSFCell[] cell = new HSSFCell[columnSize];
		for (Iterator<?> it = results.iterator(); it.hasNext();) {
			Object obj = it.next();
			row = sheet.createRow(rownum++);
			index = 0;
			for (Iterator<String> it2 = keySet.iterator(); it2.hasNext();) {
				Object value = ObjectKit.getFieldValue(obj, it2.next());
				cell[index] = row.createCell(index);
				cell[index].setCellType(HSSFCell.CELL_TYPE_STRING);
				if (value instanceof Date) {
					value = ToolsKit.formatDate((Date) value, Config.getDefaultDateFormat());
				}
				if (value instanceof Document ||
						value instanceof IdEntity ||
						value instanceof List || 
						value instanceof Map) {
					value = ToolsKit.toJsonString(value);
				}
				cell[index].setCellValue(new HSSFRichTextString(value + ""));
				index++;
			}
		}
		FileOutputStream os = null;
		String filePath = "";
		try {
			String rootPath = PathKit.getWebRootPath();
			filePath = rootPath + Config.getUploadFileServerRootDirectory() + "/excel/"
					+ ToolsKit.getCurrentDate("yyyyMMdd");
			File directory = new File(filePath);
			if (!directory.exists()) {
				if (!directory.mkdirs()) {
					throw new RuntimeException("Directory " + filePath + " not exists and can not create directory.");
				}
			}
			filePath += "/" + System.currentTimeMillis() + ".xls";
			File file = new File(filePath);
			if (!file.isFile()) {
				file.createNewFile();
			}
			os = new FileOutputStream(filePath);
			workbook.write(os);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				os.flush();
				os.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return filePath;
	}

}